package org.soenea.ass2.services.datasource;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import org.dsrg.soenea.service.threadLocal.DbRegistry;

public class GroupRDG {
	public static final String BASE_NAME = "Groups";
	public static final String TABLE = BASE_NAME;
	public static final String INSERT = "INSERT INTO " + TABLE + " (name,age) VALUES (?,?);";
	public static final String UPDATE = "UPDATE " + TABLE + " AS p set p.age=? WHERE p.name=?;";
	public static final String DELETE = "DELETE FROM " + TABLE + " AS p WHERE p.name=?;";
	
	public static final String SELECT_ALL = "SELECT * FROM " + TABLE;
	public static final String SELECT = "SELECT p.name, p.age FROM " + TABLE + " AS p WHERE p.name=?;";
	
	public static ArrayList<GroupRDG> findAll() throws SQLException {
		ArrayList<GroupRDG> result = new ArrayList<GroupRDG>();
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(SELECT_ALL);
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			result.add(new GroupRDG(rs.getString("group_name"), rs.getInt("group_id")));
		}
		ps.close();
		return result;
	}
	
	public static GroupRDG find(String name) throws SQLException {
		GroupRDG result = null;
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(SELECT);
		ps.setString(1, name);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
			result = new GroupRDG(rs.getString("group_name"), rs.getInt("group_id"));
		}
		ps.close();
		return result;
	}
	
	private String group_name;
	private int group_id;
	public GroupRDG(String group_name, int group_id) {
		super();
		this.group_name = group_name;
		this.group_id = group_id;
	}
	public int getId() {
		return group_id;
	}
	public void setId(int group_id) {
		this.group_id = group_id;
	}
	public String getName() {
		return group_name;
	}

	public void insert() throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(INSERT);
		ps.setString(1, group_name);
		ps.setInt(2, group_id);
		ps.execute();
	}

	public void update() throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(UPDATE);
		ps.setInt(1, group_id);
		ps.setString(2, group_name);
		ps.execute();		
	}

	public void delete() throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(DELETE);
		ps.setString(1, group_name);
		ps.execute();		
	}

}
